#include <stdint.h>
#include "HPCDatabase.h"
#include <HPCSensor.h>
#include <HPCAnemometer.h>
#include <HPCRainGauge.h>
#include <HPCWindVane.h>
#include <HPCHumTemp.h>
#include <HPCLight.h>
#include <fstream>
#include <iostream>
#include <stdio.h>
#include <sstream>
#include <string>
#include <errno.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include <globals.h>
#include <vector>
#include <boost/property_tree/xml_parser.hpp>
#include <boost/foreach.hpp>

using namespace std;
using namespace boost::property_tree;

HPCDatabase::HPCDatabase(string host, string usr, string pwd, string dbname, string FilePath) : HPCLogDevice(fatal), m_sql(NULL)
{
    PROFILE_THIS;

    m_host = host;
    m_usr = usr;
    m_pwd = pwd;
    m_dbName = dbname;
    m_CacheFile = FilePath;
    m_XMLConfigFile = FilePath;
    m_CacheFile.append ("_MeasureValues.db");
    m_XMLConfigFile.append ("_ServerConfig.xml");
    m_Lock = PTHREAD_MUTEX_INITIALIZER;

    connectAndRegister(host,usr,pwd,dbname);
    if (m_State != 0)
    {
        LOG(info, "write MaesureValues to file");
    }
    //ctor
}

HPCDatabase::~HPCDatabase()
{
    PROFILE_THIS;

    for ( HPCUnit::TConnection connection: m_UnitEvents )
    {
        connection.disconnect();
    }

    if (this->m_sql != NULL)
    {
        mysql_close(this->m_sql);
        LOG(info, "Disconnected from database.");
    }
}

void HPCDatabase::OnLog( const HPCLogMessage& lm )
{
    if ( lm.LogLevel() <= LogLevel() )
    {
        // TODO write log to database
    }
}

void HPCDatabase::checkStation()
{
    PROFILE_THIS;

    string macaddr;
    std::stringstream s;
    string sql;

    macaddr = this->getMacAddres();

    s << "SELECT Id, title FROM Stations WHERE maddr = \"" << macaddr << "\"";
    sql = s.str();
    LOG(debug, "sql: " << sql);

    if (!mysql_query(this->m_sql, sql.c_str()))
    {
        MYSQL_RES *result = mysql_store_result(this->m_sql);
        if (result != NULL)
        {
            int rowcount = mysql_num_rows(result);
            int fldcount = mysql_num_fields(result);
            LOG(debug, "rowcount: " << rowcount << " | fldcount: " << fldcount);
            MYSQL_ROW row;

            if (rowcount > 0)
            {
                row = mysql_fetch_row(result);
                LOG(info, "Station: " << row[0]);
                m_StationId = atoi(row[0]);
                m_StationTitle = row[1];
            }
            this->m_State = (rowcount > 0 ? 0 : 3);     // 3 = station not found in the database
            mysql_free_result(result);
        }
    }
}

string HPCDatabase::getMacAddres()
{
    PROFILE_THIS;
    string smaddr;

    if (m_MacAddress.length() == 0)
    {
        ifstream fmaddr ("/sys/class/net/eth0/address");

        if (fmaddr.is_open())
        {
            getline (fmaddr,smaddr);
            fmaddr.close();
            LOG(info, "Raspi MacAddress: " << smaddr);
            m_MacAddress = smaddr;
        }
        else
        {
            THROW( "HPCDatabase station not found!" );
        }
    }
    else
    {
        smaddr = m_MacAddress;
    }
    return smaddr;
}

void HPCDatabase::createUnits (HPCStation& station)
{
    PROFILE_THIS;

    if (this->m_State == 0)     // if the station is connected and registered to the dbata ase
    {
        createUnitsFromDB(station);
        writeStationConfig();
    }
    else
    {
        createUnitsFromXML(station);
    }
}

void HPCDatabase::createUnitsFromDB(HPCStation& station)
{
    PROFILE_THIS;
    boost::property_tree::ptree ptUnit;

    std::stringstream s;
    string sql;

    s << "SELECT id, Title, TypeId FROM Units WHERE StationId = " << this->m_StationId;
    sql = s.str();
    LOG(debug, "sql: " << sql);

    // first line of the station-configfile'
    m_ptStationConfig.put ("Station.<xmlattr>.Id",m_StationId);
    m_ptStationConfig.put ("Station.<xmlattr>.Title",m_StationTitle);

    int r = mysql_query(this->m_sql, sql.c_str());
    if ( mysql_errno(m_sql) || mysql_warning_count(m_sql) )
      r = 1; //error
    else
      r = 0; //OK

    LOG(debug, "result: " << r);

    if (r == 0)
    {
        MYSQL_RES *result = mysql_store_result(this->m_sql);
        if (result != NULL)
        {
            int rowcount = mysql_num_rows(result);
            LOG(debug, rowcount << " units and sensors found");
            MYSQL_ROW row;

            while ( (row = mysql_fetch_row(result)) )
            {
                HPCUnit* u = new HPCUnit( atoi(row[0]),row[1] );

                // one line for each unit
                ptUnit.put ("<xmlattr>.Id", row[0]);
                ptUnit.put ("<xmlattr>.Title", row[1]);
                ptUnit.put ("<xmlattr>.TypeId", row[2]);

                createSensors (*u, ptUnit);
                station += u;

                ///**< connect to the eventhandler. */
                m_UnitEvents.push_back( u->OnChange( boost::bind(&HPCDatabase::UnitChanged, this, _1, _2, _3) ) );
// TODO m_UnitAlarm.pushback
                LOG(info, u->asString() << " registered" );
                m_ptStationConfig.add_child ("Station.Unit", ptUnit);
            }
            mysql_free_result(result);
        }
    }
    else
    {
        LOG(debug, "SQL failed!");
    }
}

void HPCDatabase::createUnitsFromXML(HPCStation& station)
{
    PROFILE_THIS;

    ptree ptUnit;
    ptree ptSensor;
    ptree ptConfig;
    HPCUnit* unit;

    xml_parser::read_xml (m_XMLConfigFile,ptConfig);

//    ptStation = ptConfig.get_child("Station");
    BOOST_FOREACH (boost::property_tree::ptree::value_type& vtStation, ptConfig.get_child("Station"))
    {
        string f = vtStation.first;
//        cout << "Station first: " << f << endl;
        if (f == "<xmlattr>")
        {
            string sId = vtStation.second.get<string>("Id");
            string sTitle = vtStation.second.get<string>("Title");
            LOG ( debug, "Station: " << sTitle << "   Id: " << sId);
        }
        else
        {
            if (f == "Unit")
            {
                ptUnit = vtStation.second;

                BOOST_FOREACH (ptree::value_type& vtUnit, ptUnit.get_child(""))
                {
                    string sSt = vtUnit.first;
//                    cout << "Unit first: " << sSt << endl;
                    if (sSt == "<xmlattr>")
                    {
                        string uId = vtUnit.second.get<string>("Id");
                        string uTitle = vtUnit.second.get<string>("Title");
                        string uTId = vtUnit.second.get<string>("TypeId");
                        LOG ( debug, "    Unit: " << uTitle << "   Id: " << uId << "   TypeId: " << uTId );
                        unit = new HPCUnit( atoi(uId.c_str() ),uTitle );
                    }
                    else
                    {
                        if (vtUnit.first == "Sensor")
                        {
                            ptSensor = vtUnit.second;
                            string out = "";
                            BOOST_FOREACH (ptree::value_type& vtParm, ptSensor.get_child(""))
                            {
                                string label = vtParm.first;
//                                cout << "Sensor first: " << label << endl;
                                if (vtParm.first == "<xmlattr>")
                                {
                                    HPCSensor *sens;
                                    string sensId = vtParm.second.get<string>("Id");
                                    string sensTitle = vtParm.second.get<string>("Title");
                                    string sensTId = vtParm.second.get<string>("TypeId");
                                    string sensValType = vtParm.second.get<string>("ValueType");
                                    string sensMUnit = vtParm.second.get<string>("MeasureUnit");
                                    string sensPin = vtParm.second.get<string>("Pin");
                                    string sensInt = vtParm.second.get<string>("Interval");
                                    string sensChan = "";
                                    if (sensTId == "4")
                                    {
                                        sensChan = vtParm.second.get<string>("channel");
                                    }
                                    LOG ( debug, "        Sensor: " << sensTitle << "  Id: " << sensId << "  TypeId: " << sensTId << "  ValueType: " << sensValType << " MeasureUnit: " << sensMUnit << "  Pin: " << sensPin << "  Interval: " << sensInt << "  Channel: " << sensChan );
                                    int  st = atoi(sensTId.c_str());
                                    switch ( (HPCSensor::SensorType)st )
                                    {
                                        case HPCSensor::SensorType::HPCAnemometer:
                                        {
                                            sens = new HPCAnemometer( atoi(sensId.c_str()), atoi(sensPin.c_str()), atoi(sensInt.c_str()) );
                                        }
                                        break;
                                        case HPCSensor::SensorType::HPCRainGauge:
                                        {
                                            //LOG(info, "HPCRainGauge (sensId " << sensId << ") pin: " << pin << ", interval: " << interval);
                                            sens = new HPCRainGauge( atoi(sensId.c_str()), atoi(sensPin.c_str()), atoi(sensInt.c_str()) );
                                        }
                                        break;
                                        case HPCSensor::SensorType::HPCWindVane:
                                        {
                                            sens = new HPCWindVane (  atoi(sensId.c_str()), atoi(sensChan.c_str()), atoi(sensPin.c_str()), atoi(sensInt.c_str()));
                                        }
                                        break;
                                        case HPCSensor::SensorType::HPCHumidity:
                                        {
                                            //LOG(info, "HPCHumTemp (id " << id << ") pin: " << pin << ", interval: " << interval);
                                            sens = new HPCHumidity ( atoi(sensId.c_str()), atoi(sensPin.c_str()), atoi(sensInt.c_str()) );
                                        }
                                        break;
                                        case HPCSensor::SensorType::HPCTemperature:
                                        {
                                            //LOG(info, "HPCHumTemp (id " << id << ") pin: " << pin << ", interval: " << interval);
                                            sens = new HPCTemperature ( atoi(sensId.c_str()), atoi(sensPin.c_str()), atoi(sensInt.c_str()) );
                                        }
                                        break;
                                        case HPCSensor::SensorType::HPCLight:
                                        {
                                            //LOG(info, "HPCLight (id " << id << ") pin: " << pin << ", interval: " << interval);
                                            sens = new HPCLight ( atoi(sensId.c_str()), atoi(sensPin.c_str()), atoi(sensInt.c_str()) );
                                        }
                                        break;
                                        default:
                                        {
                                            LOG(warning, "Sensor:'" << sensTitle << "' type: '" << sensTId << "' not found!");
                                        }
                                        break;
                                    }
                                    if ( sens )
                                    {
                                        sens->Name( sensTitle);
                                        sens->MeasureUnit( sensMUnit );
//''                                      getSensorAlerts (sens, ptSens);
                                        *unit += sens;
                                        LOG(info, sens->asString() << " registered" );
                                    }
                                }
                            }
//                            cout << "    Sensor - End" << endl;
                        }
                    }
                }
//                cout << "  Unit - End" << endl;
                station += unit;
                ///**< connect to the eventhandler. */
                m_UnitEvents.push_back( unit->OnChange( boost::bind(&HPCDatabase::UnitChanged, this, _1, _2, _3) ) );
            }
        }
    }
}

void HPCDatabase::createSensors (HPCUnit& unit, boost::property_tree::ptree &ptUnit)
{
    PROFILE_THIS;

    std::stringstream s;
    std::stringstream line;
    string sql;
    boost::property_tree::ptree ptSens;
    boost::property_tree::ptree ptParm;

    s << "SELECT Id, title, typeId, ValueType, MeasureUnit FROM Sensors WHERE UnitId = " << unit.ID();
    sql = s.str();
    LOG(debug, "sql: " << sql);

    if (!mysql_query(this->m_sql, sql.c_str()))
    {

        MYSQL_RES *result = mysql_store_result(this->m_sql);
        if (result != NULL)
        {
            int rowcount = mysql_num_rows(result);
            int fldcount = mysql_num_fields(result);
            LOG(debug, "rowcount: " << rowcount << " | fldcount: " << fldcount);
            MYSQL_ROW row;

            while ( (row = mysql_fetch_row(result)) )
            {
                HPCSensor* sens;
                unsigned int pin;
                unsigned int interval;
                unsigned long id =  atol(row[0]);
                vector <unsigned long> valId;

                // one line for each sensor
                line.str("");
                ptSens.put ("<xmlattr>.Id", row[0]);
                ptSens.put ("<xmlattr>.Title", row[1]);
                ptSens.put ("<xmlattr>.TypeId", row[2]);
                ptSens.put ("<xmlattr>.ValueType", row[3]);
                ptSens.put ("<xmlattr>.MeasureUnit", row[4]);

                pin = getSensorParameter(id,"pin");
                interval = getSensorParameter(id,"interval");

                // one line for each sensorparameter
                s.str("");
                s << pin;
                ptSens.put ("<xmlattr>.Pin", s.str());
                s.str("");
                s << interval;
                ptSens.put ("<xmlattr>.Interval", s.str());

                switch ( (HPCSensor::SensorType)atoi(row[2]) )
                {
                    case HPCSensor::SensorType::HPCAnemometer:
                    {
                        sens = new HPCAnemometer( id, pin, interval );
                    }
                    break;
                    case HPCSensor::SensorType::HPCRainGauge:
                    {
                        //LOG(info, "HPCRainGauge (id " << id << ") pin: " << pin << ", interval: " << interval);
                        sens = new HPCRainGauge( id, pin, interval );
                    }
                    break;
                    case HPCSensor::SensorType::HPCWindVane:
                    {
                        unsigned int channel = getSensorParameter(id,"Channel");
                        //LOG(info, "HPCWindVane (id " << id << ") spi channel: " << channel << ", pin: " << pin << ", interval: " << interval);
                        s.str("");
                        s << channel;
                        ptSens.put ("<xmlattr>.channel", s.str());

                        sens = new HPCWindVane ( id, channel, pin, interval );
                    }
                    break;
                    case HPCSensor::SensorType::HPCHumidity:
                    {
                        //LOG(info, "HPCHumTemp (id " << id << ") pin: " << pin << ", interval: " << interval);
                        sens = new HPCHumidity ( id, pin, interval );
                    }
                    break;
                    case HPCSensor::SensorType::HPCTemperature:
                    {
                        //LOG(info, "HPCHumTemp (id " << id << ") pin: " << pin << ", interval: " << interval);
                        sens = new HPCTemperature ( id, pin, interval );
                    }
                    break;
                    case HPCSensor::SensorType::HPCLight:
                    {
                        //LOG(info, "HPCLight (id " << id << ") pin: " << pin << ", interval: " << interval);
                        sens = new HPCLight ( id, pin, interval );
                    }
                    break;
                    default:
                    {
                        LOG(warning, "Sensor:'" << row[1] << "' type: '" << row[2] << "' not found!");
                    }
                    break;
                }
                if ( sens )
                {
                    sens->Name( row[1] );
                    sens->MeasureUnit( row[4] );
                    getSensorAlerts (sens, ptSens);
                    unit += sens;
                    LOG(info, sens->asString() << " registered" );

                    ptUnit.add_child ("Sensor", ptSens);
                    ptSens.clear();
                }
            }
            mysql_free_result(result);
        }
    }
}

void HPCDatabase::getSensorAlerts (HPCSensor *sensor,boost::property_tree::ptree &ptSens)
{
    PROFILE_THIS;

    std::stringstream s;
    std::stringstream line;
    string sql;
    boost::property_tree::ptree ptAlert;

    s << "SELECT MinValue, MaxValue FROM SensorAlerts WHERE SensorId = " << sensor->ID();
    sql = s.str();
    LOG(debug, "sql: " << sql);

    if (!mysql_query(this->m_sql, sql.c_str()))
    {

        MYSQL_RES *result = mysql_store_result(this->m_sql);
        if (result != NULL)
        {
            int rowcount = mysql_num_rows(result);
            LOG(debug, "rowcount: " << rowcount);
            MYSQL_ROW row;

            while ( (row = mysql_fetch_row(result)) )
            {
                sensor->addAlarm(row[0],row[1]);
                LOG(debug, sensor->asString() << "min: " << row[0] << " max: " << row[1]);

                // one line for each sensor
                ptAlert.put ("<xmlattr>.MinValue", row[0]);
                ptAlert.put ("<xmlattr>.MaxValue", row[1]);

//                m_ptStationConfig.put ("Station.Unit.Sensor.Alert.MinValue",row[0]);
//                m_ptStationConfig.put ("Station.Unit.Sensor.Alert.MaxValue",row[1]);
            }
            ptSens.add_child ("Alerts", ptAlert);
            ptSens.clear();
            mysql_free_result(result);
        }
    }
}

void HPCDatabase::UnitChanged( HPCUnit* sender, HPCSensor* sensor, const HPCSensorValue& val )
{
    PROFILE_THIS;

    LOG(info, "Unit " << sender->Name() << ", Sensor " << sensor->Name() << " changed: " << (string)val << " " << sensor->MeasureUnit() << " (Type " << val.Type() << ")" );

    writeMeasureValue(sensor, val);
}

void HPCDatabase::UnitAlarmed( HPCUnit* sender, HPCSensor* sensor, const HPCSensorValue& val, bool bMax, unsigned int index)
{

}

void HPCDatabase::connectAndRegister(string host, string usr, string pwd, string dbname)
{
    PROFILE_THIS;
    my_bool rec = true;
    unsigned int to = 10;

    this->m_State = 99;

    //initialize MYSQL object for connections
    pthread_mutex_lock(&m_Lock);
    this->m_sql = mysql_init(m_sql);
    pthread_mutex_unlock(&m_Lock);

    if (this->m_sql == NULL)
    {
        LOG(error, "error while initializing the database");
//        throw runtime_error( "HPCDatabase initializing failed!" );
        this->m_State = 3;      // no conncetion to database
        return;
    }

    this->m_State = 2;       // initialized
    mysql_options(m_sql, MYSQL_OPT_RECONNECT, &rec);        //Enable or disable automatic reconnection to the server if the connection is found to have been lost.
    mysql_options(m_sql, MYSQL_OPT_READ_TIMEOUT,(char*)&to);
    mysql_options(m_sql, MYSQL_OPT_WRITE_TIMEOUT,(char*)&to);
    mysql_options(m_sql, MYSQL_OPT_CONNECT_TIMEOUT,(char*)&to);

    //Connect to the database
    if (mysql_real_connect(this->m_sql, host.c_str(), usr.c_str(), pwd.c_str(), dbname.c_str(), 0, NULL, 0) == NULL)
    {
        LOG(error, "error while connecting to the database");
//        throw runtime_error( "HPCDatabase connecting failed!" );
        this->m_State = 3;      // no conncetion to database
        return;
    }
    else
    {
        this->m_State = 1;       // connected
        LOG(info, "Database connection successful." << endl << "host: " << host << endl << "usr: " << usr << endl << "db: " << usr);
        this->checkStation();

        if (this->m_State == 0)     // connect and logged on
        {
            LOG(info, "Station is registered and logged on.");

            checkAndWriteLocalMeasureValues();
        }
        else
        {
            LOG(trace, "Station not found in database.");
  //          throw runtime_error( "HPCDatabase station not found!" );
        }
    }
}

unsigned int HPCDatabase::getSensorParameter (unsigned long sensId, string param)
{
    PROFILE_THIS;

    std::stringstream s;
    std::stringstream line;
    string sql;
    unsigned int res = 0;

    s << "SELECT value FROM SensorParameters WHERE SensorId = " << sensId << " AND Title =\"" << param << "\"";
    sql = s.str();
    LOG(debug, "sql: " << sql);

    if (!mysql_query(this->m_sql, sql.c_str()))
    {
        MYSQL_RES *pRes = mysql_store_result(this->m_sql);
        MYSQL_ROW row;
        int rowcount = mysql_num_rows(pRes);
        int fldcount = mysql_num_fields(pRes);
        LOG(debug, "rowcount for " << param << ": " << rowcount << "   fieldcount : " << fldcount);

        if (rowcount > 0)
        {
            if ( (row = mysql_fetch_row(pRes)) )
            {
                res = atoi(row[0]);
                LOG(debug, param << " - res: " << row[0]);
            }
        }
        mysql_free_result(pRes);
    }
    return res;
}

void HPCDatabase::writeMeasureValue(HPCSensor *sensor, const HPCSensorValue& val)
{
    PROFILE_THIS;

//    LOG(debug, sensor.Name() << ": " << (string)val );
    LOG(debug, "server_status: " << mysql_ping(m_sql) << "  m_State: " << m_State);

    pthread_mutex_lock(&m_Lock);
    // is the connection ready and the station is logged on
    if (m_State == 0)   //(_connection != NULL)
    {
        writeMeasureValueToDB (sensor, val);
    }
    else
    {
        LOG(error, "connection lost - try to connect");
        connectAndRegister(this->m_host,this->m_usr,this->m_pwd,this->m_dbName);
        if (m_State != 0)
        {
            writeMeasureValueToFile(sensor, val);
        }
        else
        {
            writeMeasureValueToDB(sensor, val);
        }
    }
    pthread_mutex_unlock(&m_Lock);
}

void HPCDatabase::writeMeasureValueToDB (HPCSensor *sensor, const HPCSensorValue& val)
{
    PROFILE_THIS;

    std::stringstream s;
    string sql;
    int query_state;

    s << "INSERT INTO MeasureValues (SensorId, Value) VALUES (" << sensor->ID() << ",\"" << (string)val << "\")";
    sql = s.str();
    LOG(debug, "sql: " << sql);

    query_state = mysql_query(this->m_sql, sql.c_str());
    LOG(debug, "query_state: " << query_state);

    if (query_state != 0)
    {
        LOG(error, "error while writing measurevalue in the database : " << mysql_error(this->m_sql) );
        writeMeasureValueToFile(sensor, val);
    }
}

void HPCDatabase::writeMeasureValueToFile (HPCSensor *sensor, const HPCSensorValue& val)
{
    PROFILE_THIS;

    std::ofstream ofs;

    pthread_mutex_lock(&m_Lock);

    ofs.open (m_CacheFile, std::ofstream::out | std::ofstream::app);
    // TODO (GS#1#): ohne value-id
    //ofs << val.ID()  << ",\"" << (string)val << "\",\"" << val.Timestamp() << "\"" << endl;
    ofs.close();
    //LOG(debug, "write to file: " << val.ID() << ",\"" << (string)val << "\",\"" << val.Timestamp() << "\"");

    pthread_mutex_unlock(&m_Lock);
}

void HPCDatabase::writeStationConfig ()
{
    PROFILE_THIS;

    pthread_mutex_lock(&m_Lock);

    boost::property_tree::xml_parser::write_xml (m_XMLConfigFile,m_ptStationConfig);
    LOG(info, "write station-configuration to file");

    pthread_mutex_unlock(&m_Lock);
}

void HPCDatabase::checkAndWriteLocalMeasureValues()
{
    PROFILE_THIS;

    ifstream f(m_CacheFile);
	vector <string> vline;
	string sline;
	string sql;
    std::stringstream s;
    int query_state;
    unsigned int i;

    while (f.good())
	{
		// lesen aus Datei -> schreiben in Database
        getline (f, sline);
        vline.push_back(sline);
    }
    f.close();

    if (vline.size() > 0)
    {
        s << "INSERT INTO MeasureValues (ValueId,Value) VALUES ";
        for (i = 0; i<vline.size();i++)         // create a singl INSERT-Statement for the whole file
        {
            if (i>0)
            {
                s << ",";
            }
            s << "(" << vline[i] << ")";
            LOG(debug, "vline[" << i << "]: " << vline[i]);
        }

        sql = s.str();
        LOG(debug, "sql: " << sql);

        pthread_mutex_lock(&m_Lock);
        query_state = mysql_query(this->m_sql, sql.c_str());
        pthread_mutex_unlock(&m_Lock);

        if (query_state != 0)
        {
            LOG(error, "error while writing measurevalue from local file in the database : " << mysql_error(this->m_sql) );
        }
        else
        {
            remove("HPC_MearureValues.txt");
            LOG(debug, "file \"HPC_MeasureValues.txt\" was written to the database and the file was deleted.");
        }
    }
    else
    {
        LOG(debug, "file \"HPC_MeasureValues.txt\" not available - nothing to do.");
    }
}
